with customer_project as (--1客户表出主数据
    select a.project_name      as project_name          --项目名称
         , a.id                as customer_id           --客户id
         , a.code              as customer_code         --客户编码
         , a.name              as customer_name         --客户名称
         , a.mall_id           as customer_mall_id      --商家id（主要对接第三方客户）
         , a.mall_name         as customer_mall_name    --商家名称
         , a.customer_category as customer_category     --客户类别（1-终端寄件，2-农特专递，3-总部共享，4-代理区共享）
         , b.id                as customer_network_id   --客户所属网点id
         , b.code              as customer_network_code --客户所属网点code
         , b.name              as customer_network_name --客户所属网点name
    from (
             select *
                  , row_number() over (partition by code order by flag desc,update_time desc ) as rn
             from (
                      select project_name --项目名称
                           , id           --客户id
                           , code         --客户编码
                           , name         --客户名称
                           , network_id   --客户所属网点
                           , mall_id      --商家id（主要对接第三方客户）
                           , mall_name    --商家名称
                           , update_time
                           , 6 as customer_category
                           , 2 as flag
                      from jms_dim.dim_lmdm_sys_customer
                      where code is not null
                      union all
                      select concat('菜鸟vip-', name) as project_name
                           , id                     as id
                           , code                   as code
                           , name                   as name
                           , null                   as network_id
                           , null                   as mall_id           --商家id（主要对接第三方客户）
                           , null                   as mall_name         --商家名称
                           , update_time            as update_time
                           , customer_category      as customer_category --客户类别（1-终端寄件，2-农特专递，3-总部共享，4-代理区共享）
                           , 1                      as flag
                      from jms_dim.dim_sys_customer_additional_base a
                      where code is not null
                        and a.name <> 'test'
                  ) a
         ) a
             left join jms_dim.dim_network_whole_massage b
                       on a.network_id = b.id
    where rn = 1
),
     order_incre as ( --订单表，取字段
         select id as order_id         --订单编号
              , waybill_id             --运单号
              , terminal_dispatch_code --三段码
              , customer_code          --客户编号code
              , customer_name          --客户编号名称
              , order_status_code      --订单状态code
              , input_time             --订单录入时间
              , pick_time              --取件时间
              , is_transfer            --是否转寄(1否 2是)
              , real_pick_network_code --实际取件网点code
              , dispatch_network_code  --派件网点code
              , destination_id         --目的地id
              , cancel_time            --订单取消时间
              , real_pick_network_name --实际取件网点名称
              , sender_province_id     --寄件省份id
              , sender_province_name   --寄件省份名称
              , sender_city_id         --寄件城市id
              , sender_city_name       --寄件城市名
              , sender_area_id         --寄件区域Id
              , sender_area_name       --寄件区域名称
              , receiver_province_id   --收件省份id
              , receiver_province_name --收件省份名称
              , receiver_city_id       --收件城市id
              , receiver_city_name     --收件城市名称
              , receiver_area_id       --收件区域id
              , receiver_area_name     --收件区域名称
              , dispatch_network_name  --派件网点name
              , order_source_code      --订单来源code
              , order_source_name      --订单来源名称
         from jms_dwd.dwd_yl_oms_oms_order_incre_dt
         where dt >= date_add('{{ execution_date | cst_ds }}', -44)
           and dt <= '{{ execution_date | cst_ds }}'
           and customer_code is not null
           and input_time is not null
     ),
     wide_unsign as (
         select waybill_no
              , input_time                  as way_input_time              --录入
              , taking_early_scantime       as taking_early_scantime       --最早揽收时间
              , deliver_early_scantime      as deliver_early_scantime      --第一次派件时间
              , outstore_early_scantime     as outstore_early_scantime     --第一次出库时间
              , instore_early_scantime      as in_store_scan_time          --第一次入库时间
              , sign_early_scantime         as sign_early_scantime         --第一次签收时间
              , last_update_time            as last_update_time            --最后更新时间
              , taking_early_scansitecode   as taking_early_scansitecode   --最早揽收网点
              , deliver_early_scansitecode  as deliver_early_scansitecode  --第一次派件网点
              , instore_early_scansitecode  as instore_early_scansitecode  --第一次入库网点
              , outstore_early_scansitecode as outstore_early_scansitecode --第一次出库网点
              , sign_early_scansitecode     as sign_early_scansitecode     --第一次签收网点
              , is_intercept                as is_intercept                --是否拦截件，1：是，0：否
              , is_difficult                as is_difficult                --是否问题件，1：是，0：否
              , is_sign                     as is_sign                     --是否签收,1是,0否
         from jms_dwd.dwd_wide_unsign_summary_waybill_dt
         where dt >= date_add('{{ execution_date | cst_ds }}', -44)
           and dt <= '{{ execution_date | cst_ds }}'
           and substr(input_time, 1, 10) >= date_add('{{ execution_date | cst_ds }}', -44)
           and substr(input_time, 1, 10) <= '{{ execution_date | cst_ds }}'
           and customer_code is not null
     ),
     all_work_order as (--客诉量 ----------------------------------------------------------------------------------------
         select waybill_no
              , work_type
              , first_type_code
              , first_type_name
              , second_type_code
              , second_type_name
              , create_time as comp_create_time
         from (
                  select waybill_no
                       , work_type
                       , first_type_code
                       , first_type_name
                       , second_type_code
                       , second_type_name
                       , row_number() over (partition by waybill_no order by flag,update_time desc ) as rn
                       , date_format(create_time, 'yyyy-MM-dd HH:mm:ss')                             as create_time
                  from (
                           select waybill_no
                                , '普通工单' as work_type
                                , first_type_code
                                , first_type_name
                                , second_type_code
                                , second_type_name
                                , update_time
                                , 1      as flag
                                , create_time
                           from jms_dwd.dwd_work_order_new_base_dt order1
                           where dt <= '{{ execution_date | cst_ds }}'
                             and dt >= date_add('{{ execution_date | cst_ds }}', -45)
                             and first_type_code = '21' --投诉
                             and waybill_no is not null
                             and waybill_no <> ''
                             and substr(waybill_no, 1, 2) <> '55'
                             and source_code not in ('1', 2)
                           union all
                           select waybill_no
                                , '理赔工单'      as work_type
                                , first_type_code
                                , first_type_name
                                , second_type_code
                                , second_type_name
                                , update_time
                                , 2           as flag
                                , update_time as create_time
                           from jms_dwd.dwd_claim_work_order_new_base_dt claim
                           where dt <= '{{ execution_date | cst_ds }}'
                             and dt >= date_add('{{ execution_date | cst_ds }}', -45)
                           union all
                           select t1.waybill_no
                                , '平台工单'                as work_type
                                , t1.first_level_code   as first_level_code
                                , '投诉'                  as first_level_name
                                , t1.first_problem_type as second_level_code
                                , t2.type_desc          as second_level_name
                                , t1.update_time
                                , 3                     as flag
                                , pdd_created_at        as create_time
                           from (
                                    select waybill_no, first_level_code, first_problem_type, update_time, pdd_created_at
                                    from jms_dwd.dwd_project_work_order_new_base_dt project-- 平台工单
                                    where dt <= '{{ execution_date | cst_ds }}'
                                      and dt >= date_add('{{ execution_date | cst_ds }}'
                                        , -45)
                                      and waybill_no is not null
                                      and waybill_no <> ''
                                      and work_order_source_id = 1 -- 消费者 工单创建来源 0：平台 1：消费者 2：商家'
                                      and (ticket_first_type = '10000' or ticket_first_type is null)
                                ) t1
                                    left join jms_dim.dim_project_work_order_type t2
                                              on t1.first_problem_type = t2.type_id
                           union all
                           select waybill_no
                                , '邮政工单'            as work_type
                                , null              as first_type_code
                                , null              as first_type_name
                                , cause             as second_type_code
                                , case
                                      when cause = 1 then '快件延误'
                                      when cause = 2 then '快件丢失短少'
                                      when cause = 3 then '快件损毁'
                                      when cause = 4 then '收寄服务'
                                      when cause = 5 then '收寄服务'
                                      when cause = 6 then '售后服务'
                                      when cause = 7 then '违规收费'
                                      when cause = 9 then '代收货款'
                                      else '其他' end as second_type_name
                                , update_time
                                , 4                 as flag
                                , create_time
                           from jms_dwd.dwd_appeal_info_new_base_dt appeal
                           where dt <= '{{ execution_date | cst_ds }}'
                             and dt >= date_add('{{ execution_date | cst_ds }}'
                               , -45)
                             and waybill_no is not null
                             and waybill_no <> ''
                       ) a
              ) b
         where rn = 1
     ),
     express_wo as (--破损量 ----------------------------------------------------------------------------------------
         select waybill_no
              , problem_type_id
              , problem_type_code
              , problem_type_name
              , problem_type_subject_id
              , problem_type_subject_code
              , problem_type_subject_name
              , problem_description
              , problem_create_time
         from (
                  select waybill_no
                       , problem_type_id                                                        as problem_type_id           --一级类型id
                       , problem_type_code                                                      as problem_type_code         --一级类型code
                       , problem_type_name                                                      as problem_type_name         --一级类型name
                       , problem_type_subject_id                                                as problem_type_subject_id   --二级类型id
                       , problem_type_subject_code                                              as problem_type_subject_code --二级类型code
                       , problem_type_subject_name                                              as problem_type_subject_name --二级类型name
                       , problem_description                                                    as problem_description       --问题描述
                       , row_number() over (partition by waybill_no order by update_time desc ) as rn
                       , create_time                                                            as problem_create_time
                  from jms_dwd.dwd_sqs_problem_express_wo_base_dt --质量工单表
                  where dt >= date_add('{{ execution_date | cst_ds }}', -44)
                    and dt <= '{{ execution_date | cst_ds }}'
                    and problem_type_code = '27'
              ) a
         where rn = 1
     ),
     arbitration_type as (--丢失量
         select waybill_no
              , first_type_code
              , first_type
              , second_type_code
              , second_type
              , create_time as arb_create_time
         from (
                  select waybill_no
                       , first_type_code
                       , first_type
                       , second_type_code
                       , second_type
                       , row_number() over (partition by waybill_no order by update_time desc ) as rn
                       , create_time
                  from jms_dwd.dwd_arbitration_base_dt --仲裁
                  where dt >= date_add('{{ execution_date | cst_ds }}', -44)
                    and dt <= '{{ execution_date | cst_ds }}'
                    and first_type_code = '03'
              ) a
         where rn = 1
     ),
     objection_info as (--虚假签收
         SELECT waybill_no
         FROM jms_dwd.dwd_sqs_objection_info_base_dt --虚假签收
         WHERE dt between date_add('{{ execution_date | cst_ds }}', -44) and '{{ execution_date | cst_ds }}'
           AND first_type_code = '03'
           AND second_type_code = '0302'
           AND source_type = 1
         group by waybill_no
     ),
     master_data as (
         select cus.project_name           --项目名称
              , cus.customer_id            --客户id
              , cus.customer_code          --客户编码
              , cus.customer_name          --客户名称
              , cus.customer_mall_id       --商家id（主要对接第三方客户）
              , cus.customer_mall_name     --商家名称
              , cus.customer_category      --客户类别（1-终端寄件，2-农特专递，3-总部共享，4-代理区共享）
              , cus.customer_network_id    --客户所属网点id
              , cus.customer_network_code  --客户所属网点code
              , cus.customer_network_name  --客户所属网点name
              , ord.order_id               --订单编码
              , ord.waybill_id             --运单号
              , ord.terminal_dispatch_code --三段码
              , ord.order_status_code      --订单状-=code
              , ord.pick_time              --订单取件时间
              , ord.input_time             --订单录入时间
              , ord.is_transfer            --订单是否转寄(1否 2是)
              , ord.real_pick_network_code --订单实际取件网点code
              , ord.real_pick_network_name --订单实际取件网点name
              , ord.dispatch_network_code  --订单派件网点code
              , ord.dispatch_network_name  --订单派件网点name
              , ord.destination_id         --订单目的地id
              , ord.cancel_time            --订单取消时间
              , ord.sender_province_id     --订单寄件省份id
              , ord.sender_province_name   --订单寄件省份名称
              , ord.sender_city_id         --订单寄件城市id
              , ord.sender_city_name       --订单寄件城市名
              , ord.sender_area_id         --订单寄件区域Id
              , ord.sender_area_name       --订单寄件区域名称
              , ord.receiver_province_id   --订单收件省份id
              , ord.receiver_province_name --订单收件省份名称
              , ord.receiver_city_id       --订单收件城市id
              , ord.receiver_city_name     --订单收件城市名称
              , ord.receiver_area_id       --订单收件区域id
              , ord.receiver_area_name     --订单收件区域名称
              , ord.order_source_code      --订单来源code
              , ord.order_source_name      --订单来源名称
         from order_incre ord
                  left join customer_project cus
                            on cus.customer_code = ord.customer_code
     )
insert
overwrite
table
jms_dwd.dwd_wide_customer_order_waybill_dt
partition
(
dt
)
select mas.project_name                     as project_name                --项目名称
     , mas.customer_id                      as customer_id                 --客户id
     , mas.customer_code                    as customer_code               --客户编码
     , mas.customer_name                    as customer_name               --客户名称
     , mas.customer_mall_id                 as customer_mall_id            --商家id（主要对接第三方客户）
     , mas.customer_mall_name               as customer_mall_name          --商家名称
     , mas.customer_category                as customer_category           --客户类别（1-终端寄件，2-农特专递，3-总部共享，4-代理区共享）
     , mas.customer_network_id              as customer_network_id         --客户所属网点id
     , mas.customer_network_code            as customer_network_code       --客户所属网点code
     , mas.customer_network_name            as customer_network_name       --客户所属网点name
     , mas.order_id                         as order_id                    --订单编码
     , mas.waybill_id                       as waybill_id                  --运单号
     , mas.terminal_dispatch_code           as terminal_dispatch_code      --三段码
     , mas.order_status_code                as order_status_code           --订单状态code
     , mas.pick_time                        as pick_time                   --订单取件时间
     , mas.input_time                       as input_time                  --订单录入时间
     , mas.is_transfer                      as is_transfer                 --订单是否转寄(1否 2是)
     , mas.real_pick_network_code           as real_pick_network_code      --订单实际取件网点code
     , mas.real_pick_network_name           as real_pick_network_name      --订单实际取件网点name
     , mas.dispatch_network_code            as dispatch_network_code       --订单派件网点code
     , mas.dispatch_network_name            as dispatch_network_name       --订单派件网点name
     , mas.destination_id                   as destination_id              --订单目的地id
     , mas.cancel_time                      as cancel_time                 --订单取消时间
     , mas.sender_province_id               as sender_province_id          --订单寄件省份id
     , mas.sender_province_name             as sender_province_name        --订单寄件省份名称
     , mas.sender_city_id                   as sender_city_id              --订单寄件城市id
     , mas.sender_city_name                 as sender_city_name            --订单寄件城市名
     , mas.sender_area_id                   as sender_area_id              --订单寄件区域Id
     , mas.sender_area_name                 as sender_area_name            --订单寄件区域名称
     , mas.receiver_province_id             as receiver_province_id        --订单收件省份id
     , mas.receiver_province_name           as receiver_province_name      --订单收件省份名称
     , mas.receiver_city_id                 as receiver_city_id            --订单收件城市id
     , mas.receiver_city_name               as receiver_city_name          --订单收件城市名称
     , mas.receiver_area_id                 as receiver_area_id            --订单收件区域id
     , mas.receiver_area_name               as receiver_area_name          --订单收件区域名称
     , mas.order_source_code                as order_source_code           --订单来源code
     , mas.order_source_name                as order_source_name           --订单来源名称
     , unsign.way_input_time                as way_input_time              --宽表运单录入
     , unsign.taking_early_scantime         as taking_early_scantime       --宽表最早揽收时间
     , unsign.deliver_early_scantime        as deliver_early_scantime      --宽表第一次派件时间
     , unsign.outstore_early_scantime       as outstore_early_scantime     --宽表第一次出库时间
     , unsign.in_store_scan_time            as in_store_scan_time          --宽表第一次入库时间
     , unsign.sign_early_scantime           as sign_early_scantime         --宽表第一次签收时间
     , unsign.last_update_time              as last_update_time            --宽表最后更新时间
     , unsign.taking_early_scansitecode     as taking_early_scansitecode   --宽表最早揽收网点
     , unsign.deliver_early_scansitecode    as deliver_early_scansitecode  --宽表第一次派件网点
     , unsign.instore_early_scansitecode    as instore_early_scansitecode  --宽表第一次入库网点
     , unsign.outstore_early_scansitecode   as outstore_early_scansitecode --宽表第一次出库网点
     , unsign.sign_early_scansitecode       as sign_early_scansitecode     --宽表第一次签收网点
     , unsign.is_intercept                  as is_intercept                --宽表是否拦截件，1：是，0：否
     , unsign.is_difficult                  as is_difficult                --宽表是否问题件，1：是，0：否
     , unsign.is_sign                       as is_sign                     --宽表是否签收,1是,0否
     , if(wor.waybill_no is not null, 1, 0) as work_order_join             --客诉标识,1代表客诉0代表非客诉
     , if(arb.waybill_no is not null, 1, 0) as arbitration_join            --丢失标识 ,1代表丢失,0代表未丢失
     , if(exp.waybill_no is not null, 1, 0) as express_join                --破损标识 1,代表破损,0未破损
     , if(obj.waybill_no is not null, 1, 0) as xujia_sign_join             --虚假签收标识 1,代表虚假签收,0未虚假签收
     , exp.problem_type_id                  as problem_type_id             --破损量问题件类型id（基础数据的一级类型id）
     , exp.problem_type_code                as problem_type_code           --破损量问题件类型编码
     , exp.problem_type_name                as problem_type_name           --破损量问题件类型名称
     , exp.problem_type_subject_id          as problem_type_subject_id     --破损量问题件二级类型id（基础数据的二级类型id）
     , exp.problem_type_subject_code        as problem_type_subject_code   --破损量问题件二级类型编码
     , exp.problem_type_subject_name        as problem_type_subject_name   --破损量问题件二级类型名称
     , exp.problem_description              as problem_description         --破损量问题件原因
     , wor.first_type_code                  as first_type_code             --客诉量一级问题类型编码
     , wor.first_type_name                  as first_type_name             --客诉量一级问题类型
     , wor.second_type_code                 as second_type_code            --客诉量二级问题类型编码
     , wor.second_type_name                 as second_type_name            --客诉量二级问题类型
     , arb.first_type_code                  as arb_first_type_code         --遗失量一级问题类型编码
     , arb.first_type                       as arb_first_type              --遗失量一级问题类型
     , arb.second_type_code                 as arb_second_type_code        --遗失量二级问题类型编码
     , arb.second_type                      as arb_second_type             --遗失量二级问题类型
     , substr(input_time, 1, 10)            as date_time                   --订单录入日期
     , wor.work_type                        as work_type                   --工单类型
     , wor.comp_create_time                 as comp_create_time            --客诉登记时间
     , arb.arb_create_time                  as arb_create_time             --仲裁申报时间
     , exp.problem_create_time              as problem_create_time         --质量申报时间
     , substr(input_time, 1, 10)            as dt
from master_data mas
         left join wide_unsign unsign
                   on mas.waybill_id = unsign.waybill_no
         left join all_work_order wor
                   on mas.waybill_id = wor.waybill_no
         left join arbitration_type arb
                   on mas.waybill_id = arb.waybill_no
         left join express_wo exp
                   on mas.waybill_id = exp.waybill_no
         left join objection_info obj
                   on mas.waybill_id = obj.waybill_no
    distribute by dt
   , pmod(hash(order_id), 100)
;

